require 'optparse'

$options = {}
parser = OptionParser.new("", 24) do |opts|
  opts.banner = "\nVersion: 1.0 (Jan-2014)\n\n"

  opts.on("-i", "--input INPUT", "Input database file") do |v|
    $options[:input] = v
  end

  opts.on("-o", "--output OUTPUT", "Output CSV file") do |v|
    $options[:output] = v
  end

  opts.on("-l", "--limit N", "number of records") do |v|
    $options[:limit] = v
  end

  opts.on_tail('-h', '--help', 'Displays this help') do
		puts opts, ""
	end
end

begin
  parser.parse!
rescue SystemExit => ex
  exit
rescue Exception => ex
  puts "\nERROR: #{ex.message}\n\nRun ruby to_xls.rb -h for help\n\n"
  exit
end

if $options[:input].nil?
  puts "\nPlease specify the input: -i\n\n"
  exit
end

if $options[:output].nil?
  puts "\nPlease specify the output file: -o\n\n"
  exit
end

$options[:limit] ||= 1000000000
$options[:limit] = $options[:limit].to_i

require 'digest/sha1'
require 'sqlite3'
require 'rubygems'
require 'active_record'
require 'csv'
require 'nokogiri'
require 'zlib'
require 'spreadsheet'

class String
  def floatify
    return nil if self.nil?
    return nil if self.empty?
    return self.gsub(/[^0-9\.]/, '').to_f
  end

  def deflate
    Zlib.deflate(self)
  end

  def inflate
    Zlib.inflate(self)
  end

  def clean
    replace = {
      '【' => '[', 
      '】' => ']'
    }.each do |k,v|
      self.gsub!(Nokogiri::HTML(k).text, v)
    end
    
    self.strip!
    return self
  end

  def html2text
    r = self.strip
    # strip <style>, <script> tag
    r.gsub!(/<style\s.*>.*<\/style>/m, '')
    r.gsub!(/<script\s.*>.*<\/script>/m, '')

    # replace <tag>text</tag> with text\n
    r.gsub!(/<[a-zA-Z0-9]+\s*[^>]*>/, '')
    r.gsub!(/<\/[a-zA-Z0-9]+>/, "\n")

    # replace html entities with chars
    {'&amp;' => '&'}.each do |k,v|
      r.gsub!(k, v)
    end
    
    # strip redundant spaces
    #r.gsub!(/^\s+/, "")
    #r.gsub!(/\s+$/, "")
    #r.gsub!(/[\t\n]+/, "\n")
    #r.gsub!(/[\n]+/, "\n")
    
    return r
  end

  def to_utf8
    return self.force_encoding('UTF-8').encode("UTF-8", :invalid => :replace, :replace => '')
  end
end

ActiveRecord::Base.establish_connection(
  :adapter => 'sqlite3',
  :database => $options[:input],
  :timeout => 15000 # You'll only get the BusyException if it takes longer than 15 seconds.
)

class Item < ActiveRecord::Base
  serialize :meta, JSON
end

ActiveSupport.escape_html_entities_in_json = false

book = Spreadsheet::Workbook.new
sheet1 = book.create_worksheet

count = Item.count

SITE = 'http://abtec.co.nz/'

max_image_count = 1

Item.limit($options[:limit]).each_with_index do |item, index|
  p "#{index} / #{count}"
  row = sheet1.row(index + 1)
  ps = Nokogiri::HTML(item.html.inflate.to_utf8)

  catname = item.meta['catname']
  caturl = File.join(SITE, item.meta['caturl'])
  subname = item.meta['subname']
  suburl = File.join(SITE, item.meta['suburl']) if item.meta['suburl']
  url = item.url
  name = ps.css('#contwrap > h1').first.text.strip
  desc = [ps.css('.productinfo > h2').first.text.strip.to_utf8, item.desc.inflate.html2text.to_utf8].join("\n\n")
  rrp = ps.css('div.mincart').text[/(?<=RRP..)[\$0-9,\.]*/]
  price = ps.css('div.mincart').text[/(?<=Our Price..)[\$0-9,\.]*/]
  images = ps.css('div.prodimgs > a.gallry > img').map{|img| File.join(SITE, img.attributes['src'].value) }
  max_image_count = (images.count > max_image_count) ? images.count : max_image_count

  row.push catname, caturl, subname, suburl, url, name, desc, rrp, price
  images.each do |img|
    row.push img
  end
end

sheet1.row(0).push 'Category', 'Category URL', 'Sub-category', 'Sub-category URL', 'URL', 'Name', 'Description', 'RRP', 'Price'
1.upto(max_image_count) do |i|
  sheet1.row(0).push "Image #{i}"
end

book.write($options[:output])
puts "Done"